package tgc.edu.yzy.jdbc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import tgc.edu.yzy.bean.CocBinding;

public class CocBindingJDBC {
	
	public CocBinding get(String qqcode,String msg) {
		String sql ="SELECT * from coc_binding t where t.qqcode=? and t.msg=?";
		Connection conn = JDBC.conn();
		try {
			CallableStatement prepareCall = conn.prepareCall(sql);
			prepareCall.setString(1, qqcode);
			prepareCall.setString(2, msg);
			ResultSet rs = prepareCall.executeQuery();
			if(rs.next()) {
				CocBinding coc = new CocBinding();
				coc.setId(rs.getInt(1));
				coc.setMsg(rs.getString(4));
				coc.setQqcode(rs.getString(5));
				coc.setTag(rs.getString(6));
				coc.setType(rs.getString(7));
				rs.close();
				conn.close();
				return coc;
			}
			rs.close();
			prepareCall.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public void save(CocBinding coc) {
		String INSERT =""; 
		try {
			Connection conn = JDBC.conn();
			if(coc.getId()!=null) {
				String sql = "select * from coc_binding t where t.id=?"; 
				CallableStatement pr = conn.prepareCall(sql);
				pr.setInt(1, coc.getId());
				ResultSet re = pr.executeQuery();
				if(re.next()) {
					INSERT ="UPDATE coc_binding t SET t.msg=?,t.qqcode=?,t.tag=?,t.type=? where t.id=?";
					CallableStatement insertre = conn.prepareCall(INSERT);
					insertre.setString(1,coc.getMsg()==null?re.getString(4):coc.getMsg() );
					insertre.setString(2,coc.getQqcode()==null?re.getString(5):coc.getQqcode() );
					insertre.setString(3,coc.getTag()==null?re.getString(6):coc.getTag() );
					insertre.setString(4,coc.getType()==null?re.getString(7):coc.getType() );
					insertre.setInt(5, coc.getId());
					insertre.execute();
					insertre.close();
				}else {
					INSERT = "INSERT INTO coc_binding VALUES (?,null,null,?,?,?,?)";
					CallableStatement insertre = conn.prepareCall(INSERT);
					insertre.setInt(1, coc.getId());
					insertre.setString(2,coc.getMsg() );
					insertre.setString(3,coc.getQqcode() );
					insertre.setString(4,coc.getTag() );
					insertre.setString(5,coc.getType() );
					insertre.execute();
					insertre.close();
				}
			}else {
				INSERT = "INSERT INTO coc_binding VALUES (null,null,null,?,?,?,?)";
				CallableStatement insertre = conn.prepareCall(INSERT);
				insertre.setString(1,coc.getMsg());
				insertre.setString(2,coc.getQqcode() );
				insertre.setString(3,coc.getTag() );
				insertre.setString(4,coc.getType() );
				insertre.execute();
				insertre.close();
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public List<CocBinding> list(String qqcode){
		List<CocBinding> coc = new ArrayList<CocBinding>();
		String select = "select * from coc_binding t where t.qqcode=?";
		Connection conn = JDBC.conn();
		try {
			CallableStatement insertre = conn.prepareCall(select);
			insertre.setString(1, qqcode);
			ResultSet rs = insertre.executeQuery();
			while (rs.next()) {
				CocBinding binding = new CocBinding();
				binding.setId(rs.getInt(1));
				binding.setMsg(rs.getString(4));
				binding.setQqcode(rs.getString(5));
				binding.setTag(rs.getString(6));
				binding.setType(rs.getString(7));
				coc.add(binding);
			}
			rs.close();
			insertre.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return coc;
	}
	/*public static void main(String[] args) {
		CocBindingJDBC jdbc =new CocBindingJDBC();
		CocBinding cocBinding = jdbc.get("9366422841", "我的村庄");
		System.out.println(cocBinding.toString());
		CocBinding a = new CocBinding();
		a.setId(8);
		a.setQqcode("s");
		//a.setType("ddd");
		jdbc.save(a);
		jdbc.removeById(a);
		List<CocBinding> list = jdbc.list("936642284");
		for (CocBinding cocBinding : list) {
			System.out.println(cocBinding.toString());
		}
	}
*/
	public void removeById(CocBinding qq) {
		String delete = "DELETE FROM coc_binding  WHERE id=?";
		Connection conn = JDBC.conn();
		try {
			CallableStatement insertre = conn.prepareCall(delete);
			insertre.setInt(1, qq.getId());
			insertre.execute();
			insertre.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
